﻿using Carer.Crm.Authorize;
using Carer.Data;
using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Mvc;
using Carer.Data.Models;
using System.Data;
using Carer.Crm.Export;

namespace Carer.Crm.Controllers
{
    [SessionAuthorize]
    public class ExportController : BaseController
    {
        [HttpPost]
        public ActionResult Download(string fileName)
        {
            string configFolder = System.Configuration.ConfigurationManager.AppSettings["AttachmentPath"] + "";
            string filePath = Path.Combine(configFolder, fileName);
            byte[] bytes = System.IO.File.ReadAllBytes(filePath);

            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/octet-stream";

            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(fileName));
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();

            return new EmptyResult();
        }

        public JsonResult QueryWork(WorkQuery q, bool keep = false)
        {
            if (keep)
            {
                q = base.Session["WorkQuery"] as WorkQuery;
                if (q == null)
                {
                    base.Session["WorkQuery"] = q = new WorkQuery();
                }
            }
            else
            {
                base.Session["WorkQuery"] = q;
            }
            q.UnitId = this.GetUserUnitId();

            var listData = DataService.GetSchedulesAll(q);
            var groupData = listData.GroupBy<Schedule, Crew>(it => it.Crew).OrderBy(it => it.Key.Id);

            DataTable data = new DataTable();
            for (int i = 0; i < 14; i++)
            {
                DataColumn col = new DataColumn("col" + i, typeof(string));
                data.Columns.Add(col);
            }

            #region 表头
            DataRow row = data.NewRow();
            row[0] = "人员姓名";
            row[1] = "人员性质";
            row[2] = "服务时间";
            row[3] = "服务类别";
            row[4] = "服务项目";
            row[5] = "班次";
            row[6] = "小时数";
            row[7] = "客户姓名";
            row[8] = "区号";
            row[9] = "编号";
            row[10] = "所属公司";
            row[11] = "调整金额";
            row[12] = "结算金额";
            row[13] = "备注";
            data.Rows.Add(row);
            #endregion

            int sumHours = 0;
            int sumCustomerCount = 0;
            double sumAdjust = 0;
            double sumPay = 0;
            foreach (var item in groupData)
            {
                var crew = item.Key;
                int groupCrewIndex = 0;
                var groupStart = item.GroupBy<Schedule, string>(it => it.Start.HasValue ? it.Start.Value.ToString("yyyy-MM-dd") : string.Empty);
                foreach (var innerItem in groupStart)
                {
                    #region 人员分组
                    string startTime = innerItem.Key;
                    int groupStartIndex = 0;
                    int hours = 0;
                    int customerCount = 0;
                    double adjust = 0;
                    double pay = 0;
                    foreach (var schedule in innerItem)
                    {
                        #region 时间分组
                        row = data.NewRow();
                        //row[0] = groupStartIndex == 0 ? crew.Name : string.Empty;
                        //row[1] = groupStartIndex == 0 ? crew.Type : string.Empty;
                        //row[2] = groupStartIndex == 0 ? startTime : string.Empty;
                        row[0] = crew.Name;
                        row[1] = crew.Type;
                        row[2] = startTime;
                        row[3] = schedule.ServiceType;
                        row[4] = schedule.ServiceName;
                        row[5] = schedule.ShiftName;
                        row[6] = schedule.Hours;
                        row[7] = schedule.CustomerName;
                        row[8] = schedule.Area;
                        row[9] = schedule.Bed;
                        row[10] = schedule.Company;
                        row[11] = schedule.Adjust.HasValue ? schedule.Adjust.Value : 0;
                        row[12] = schedule.Pay.HasValue ? schedule.Pay.Value : 0;
                        row[13] = schedule.Note;
                        data.Rows.Add(row);

                        groupCrewIndex++;
                        groupStartIndex++;
                        hours += schedule.Hours;
                        customerCount++;
                        adjust += schedule.Adjust.HasValue ? schedule.Adjust.Value : 0;
                        pay += schedule.Pay.HasValue ? schedule.Pay.Value : 0;
                        #endregion
                    }
                    //小计
                    row = data.NewRow();
                    //row[0] = string.Empty;
                    //row[1] = string.Empty;
                    row[0] = crew.Name;
                    row[1] = crew.Type;
                    row[2] = "小计";
                    row[3] = string.Empty;
                    row[4] = string.Empty;
                    row[5] = string.Empty;
                    row[6] = hours;
                    row[7] = customerCount + "人";
                    row[8] = string.Empty;
                    row[9] = string.Empty;
                    row[10] = string.Empty;
                    row[11] = adjust;
                    row[12] = pay;
                    row[13] = string.Empty;
                    data.Rows.Add(row);

                    sumHours += hours;
                    sumCustomerCount += customerCount;
                    sumAdjust += adjust;
                    sumPay += pay;
                    #endregion
                }

            }

            #region 合计
            row = data.NewRow();
            row[0] = "合计";
            row[1] = string.Empty;
            row[2] = string.Empty;
            row[3] = string.Empty;
            row[4] = string.Empty;
            row[5] = string.Empty;
            row[6] = sumHours;
            row[7] = sumCustomerCount + "人";
            row[8] = string.Empty;
            row[9] = string.Empty;
            row[10] = string.Empty;
            row[11] = sumAdjust;
            row[12] = sumPay;
            row[13] = string.Empty;
            data.Rows.Add(row);
            #endregion

            #region 合并单元格
            //List<string> mergeCells = new List<string>();
            //if (data.Rows.Count > 3)
            //{
            //    //合计
            //    string item = string.Format("{0}:{1}", "A" + data.Rows.Count, "C" + data.Rows.Count);
            //    mergeCells.Add(item);
            //    //去掉首尾两行
            //    int mergeRowIndex = 0;
            //    int maxRowIndex = data.Rows.Count;
            //    for (int i = 1; i < maxRowIndex; i++)
            //    {
            //        if (!String.IsNullOrEmpty(data.Rows[i][0].ToString()))
            //        {
            //            int beginIndex = mergeRowIndex + 1;
            //            int endIndex = i;
            //            if (beginIndex != endIndex)
            //            {
            //                //人员姓名
            //                item = string.Format("{0}:{1}", "A" + beginIndex, "A" + endIndex);
            //                mergeCells.Add(item);
            //                //人员性质
            //                item = string.Format("{0}:{1}", "B" + beginIndex, "B" + endIndex);
            //                mergeCells.Add(item);
            //            }

            //            mergeRowIndex = i;
            //        }
            //    }
            //    mergeRowIndex = 0;
            //    for (int i = 1; i < maxRowIndex; i++)
            //    {
            //        if (!String.IsNullOrEmpty(data.Rows[i][2].ToString()))
            //        {
            //            int beginIndex = mergeRowIndex + 1;
            //            int endIndex = i;
            //            if (beginIndex != endIndex)
            //            {
            //                //服务时间
            //                item = string.Format("{0}:{1}", "C" + beginIndex, "C" + endIndex);
            //                mergeCells.Add(item);
            //            }

            //            mergeRowIndex = i;
            //        }
            //    }


            //}
            #endregion

            string fileName = string.Format("QueryWork_{0}_{1}.xlsx", this.GetUserId(), DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss_fff"));
            string configFolder = System.Configuration.ConfigurationManager.AppSettings["AttachmentPath"] + "";
            string filePath = Path.Combine(configFolder, fileName);

            //ExcelExportModel model = new ExcelExportModel
            //{
            //    ExportData = data,
            //    MergeCells = mergeCells
            //};
            //new WorkViewExporter().CreatePackage(filePath, model);

            MergeCellParam param = new MergeCellParam();
            param.StartRow = 1;
            param.EndRow = data.Rows.Count - 2;
            param.StartCol = 0;
            param.EndCol = 2;

            ExcelHelper.ExportDataTable(data, filePath, param, 1);

            SubmitResult result = new SubmitResult(true, fileName);
            return base.Json(result, System.Web.Mvc.JsonRequestBehavior.AllowGet);
        }

        public JsonResult QueryPay(PayQuery q, bool keep = false)
        {
            if (keep)
            {
                q = base.Session["PayQuery"] as PayQuery;
                if (q == null)
                {
                    base.Session["PayQuery"] = q = new PayQuery();
                }
            }
            else
            {
                base.Session["PayQuery"] = q;
            }
            q.UnitId = this.GetUserUnitId();

            var listData = DataService.GetSchedulesAll(q);
            var groupData = listData.GroupBy<Schedule, Crew>(it => it.Crew).OrderBy(it => it.Key.Id);

            DataTable data = new DataTable();
            for (int i = 0; i < 14; i++)
            {
                DataColumn col = new DataColumn("col" + i, typeof(string));
                data.Columns.Add(col);
            }
            #region 表头
            DataRow row = data.NewRow();
            row[0] = "人员姓名";
            row[1] = "人员性质";
            row[2] = "服务时间";
            row[3] = "服务类别";
            row[4] = "服务项目";
            row[5] = "班次";
            row[6] = "小时数";
            row[7] = "客户姓名";
            row[8] = "区号";
            row[9] = "编号";
            row[10] = "所属公司";
            row[11] = "调整金额";
            row[12] = "结算金额";
            row[13] = "备注";
            data.Rows.Add(row);
            #endregion

            int sumHours = 0;
            int sumCustomerCount = 0;
            double sumAdjust = 0;
            double sumPay = 0;
            foreach (var item in groupData)
            {
                var crew = item.Key;
                var groupStart = item.GroupBy<Schedule, string>(it => it.Start.HasValue ? it.Start.Value.ToString("yyyy-MM-dd") : string.Empty);
                foreach (var innerItem in groupStart)
                {
                    #region 人员分组
                    string startTime = innerItem.Key;
                    int groupStartIndex = 0;
                    int hours = 0;
                    int customerCount = 0;
                    double adjust = 0;
                    double pay = 0;
                    foreach (var schedule in innerItem)
                    {
                        #region 时间分组
                        row = data.NewRow();
                        //row[0] = groupStartIndex == 0 ? crew.Name : string.Empty;
                        //row[1] = groupStartIndex == 0 ? crew.Type : string.Empty;
                        //row[2] = groupStartIndex == 0 ? startTime : string.Empty;
                        row[0] = crew.Name;
                        row[1] = crew.Type;
                        row[2] = startTime;
                        row[3] = schedule.ServiceType;
                        row[4] = schedule.ServiceName;
                        row[5] = schedule.ShiftName;
                        row[6] = schedule.Hours;
                        row[7] = schedule.CustomerName;
                        row[8] = schedule.Area;
                        row[9] = schedule.Bed;
                        row[10] = schedule.Company;
                        row[11] = schedule.Adjust.HasValue ? schedule.Adjust.Value : 0;
                        row[12] = schedule.Pay.HasValue ? schedule.Pay.Value : 0;
                        row[13] = schedule.Note;
                        data.Rows.Add(row);

                        groupStartIndex++;
                        hours += schedule.Hours;
                        customerCount++;
                        adjust += schedule.Adjust.HasValue ? schedule.Adjust.Value : 0;
                        pay += schedule.Pay.HasValue ? schedule.Pay.Value : 0;
                        #endregion
                    }
                    //小计
                    row = data.NewRow();
                    //row[0] = string.Empty;
                    //row[1] = string.Empty;
                    row[0] = crew.Name;
                    row[1] = crew.Type;
                    row[2] = "小计";
                    row[3] = string.Empty;
                    row[4] = string.Empty;
                    row[5] = string.Empty;
                    row[6] = hours;
                    row[7] = customerCount;
                    row[8] = string.Empty;
                    row[9] = string.Empty;
                    row[10] = string.Empty;
                    row[11] = adjust;
                    row[12] = pay;
                    row[13] = string.Empty;
                    data.Rows.Add(row);

                    sumHours += hours;
                    sumCustomerCount += customerCount;
                    sumAdjust += adjust;
                    sumPay += pay;
                    #endregion
                }

            }

            #region 合计
            row = data.NewRow();
            row[0] = "合计";
            row[1] = string.Empty;
            row[2] = string.Empty;
            row[3] = string.Empty;
            row[4] = string.Empty;
            row[5] = string.Empty;
            row[6] = sumHours;
            row[7] = sumCustomerCount;
            row[8] = string.Empty;
            row[9] = string.Empty;
            row[10] = string.Empty;
            row[11] = sumAdjust;
            row[12] = sumPay;
            row[13] = string.Empty;
            data.Rows.Add(row);
            #endregion

            #region 合并单元格
            //List<string> mergeCells = new List<string>();
            //if (data.Rows.Count > 3)
            //{
            //    //合计
            //    string item = string.Format("{0}:{1}", "A" + data.Rows.Count, "C" + data.Rows.Count);
            //    mergeCells.Add(item);
            //    //去掉首尾两行
            //    int mergeRowIndex = 0;
            //    int maxRowIndex = data.Rows.Count;
            //    for (int i = 1; i < maxRowIndex; i++)
            //    {
            //        if (!String.IsNullOrEmpty(data.Rows[i][0].ToString()))
            //        {
            //            int beginIndex = mergeRowIndex + 1;
            //            int endIndex = i;
            //            if (beginIndex != endIndex)
            //            {
            //                //人员姓名
            //                item = string.Format("{0}:{1}", "A" + beginIndex, "A" + endIndex);
            //                mergeCells.Add(item);
            //                //人员性质
            //                item = string.Format("{0}:{1}", "B" + beginIndex, "B" + endIndex);
            //                mergeCells.Add(item);
            //            }

            //            mergeRowIndex = i;
            //        }
            //    }
            //    mergeRowIndex = 0;
            //    for (int i = 1; i < maxRowIndex; i++)
            //    {
            //        if (!String.IsNullOrEmpty(data.Rows[i][2].ToString()))
            //        {
            //            int beginIndex = mergeRowIndex + 1;
            //            int endIndex = i;
            //            if (beginIndex != endIndex)
            //            {
            //                //服务时间
            //                item = string.Format("{0}:{1}", "C" + beginIndex, "C" + endIndex);
            //                mergeCells.Add(item);
            //            }

            //            mergeRowIndex = i;
            //        }
            //    }


            //}
            #endregion

            string fileName = string.Format("QueryPay_{0}_{1}.xlsx", this.GetUserId(), DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss_fff"));
            string configFolder = System.Configuration.ConfigurationManager.AppSettings["AttachmentPath"] + "";
            string filePath = Path.Combine(configFolder, fileName);

            //ExcelExportModel model = new ExcelExportModel
            //{
            //    ExportData = data,
            //    MergeCells = mergeCells
            //};

            //new PayViewExporter().CreatePackage(filePath, model);

            MergeCellParam param = new MergeCellParam();
            param.StartRow = 1;
            param.EndRow = data.Rows.Count - 2;
            param.StartCol = 0;
            param.EndCol = 2;

            ExcelHelper.ExportDataTable(data, filePath, param, 1);

            SubmitResult result = new SubmitResult(true, fileName);
            return base.Json(result, System.Web.Mvc.JsonRequestBehavior.AllowGet);
        }

        public JsonResult QueryWage(WageQuery q)
        {
            q.UnitId = this.GetUserUnitId();
            IList<Wage> listData = DataService.GetWages(q);

            DataTable data = new DataTable();
            for (int i = 0; i < 10; i++)
            {
                DataColumn col = new DataColumn("col" + i, typeof(string));
                data.Columns.Add(col);
            }

            #region 表头
            DataRow row = data.NewRow();
            row[0] = "人员编号";
            row[1] = "人员姓名";
            row[2] = "出勤天数";
            row[3] = "服务人数";
            row[4] = "调整合计";
            row[5] = "结算款合计";
            row[6] = "奖惩合计";
            row[7] = "实发合计";
            row[8] = "签收";
            row[9] = "备注";
            data.Rows.Add(row);
            #endregion

            int sumUserCount = 0;
            double sumDayCount = 0;
            int sumCustomerCount = 0;
            double sumAdjust = 0;
            double sumPay = 0;
            double sumBonus = 0;
            foreach (var item in listData)
            {
                #region 遍历数据
                row = data.NewRow();
                row[0] = item.Id;
                row[1] = item.Name;
                row[2] = item.DayCount;
                row[3] = item.CustomerCount;
                row[4] = item.Adjust.HasValue ? item.Adjust.Value : 0;
                row[5] = item.Pay.HasValue ? item.Pay.Value : 0;
                row[6] = item.Bonus.HasValue ? item.Bonus.Value : 0;
                row[7] = (item.Pay.HasValue ? item.Pay.Value : 0) + (item.Bonus.HasValue ? item.Bonus.Value : 0);
                row[8] = string.Empty;
                row[9] = string.Empty;
                data.Rows.Add(row);

                sumUserCount++;
                sumDayCount += item.DayCount;
                sumCustomerCount += item.CustomerCount;
                sumAdjust += item.Adjust.HasValue ? item.Adjust.Value : 0;
                sumPay += item.Pay.HasValue ? item.Pay.Value : 0;
                sumBonus += item.Bonus.HasValue ? item.Bonus.Value : 0;
                #endregion
            }

            #region 合计
            row = data.NewRow();
            row[0] = "合计";
            row[1] = sumUserCount + "人";
            row[2] = sumDayCount;
            row[3] = sumCustomerCount;
            row[4] = sumAdjust;
            row[5] = sumPay;
            row[6] = sumBonus;
            row[7] = sumPay + sumBonus;
            row[8] = string.Empty;
            row[9] = string.Empty;
            data.Rows.Add(row);
            #endregion

            string fileName = string.Format("QueryWage_{0}_{1}.xlsx", this.GetUserId(), DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss_fff"));
            string configFolder = System.Configuration.ConfigurationManager.AppSettings["AttachmentPath"] + "";
            string filePath = Path.Combine(configFolder, fileName);

            //ExcelExportModel model = new ExcelExportModel
            //{
            //    ExportData = data,
            //    MergeCells = new List<string>()
            //};

            //new WageViewExporter().CreatePackage(filePath, model);

            MergeCellParam param = new MergeCellParam();
            param.StartRow = 1;
            param.EndRow = 1;
            param.StartCol = 0;
            param.EndCol = 0;

            ExcelHelper.ExportDataTable(data, filePath, param, 1);

            SubmitResult result = new SubmitResult(true, fileName);
            return base.Json(result, System.Web.Mvc.JsonRequestBehavior.AllowGet);
        }

        public JsonResult QueryRevenue(RevenueQuery q)
        {
            q.UnitId = this.GetUserUnitId();
            q.State = 0x100;
            IList<Order> listData = DataService.GetOrders(q);

            DataTable data = new DataTable();
            for (int i = 0; i < 25; i++)
            {
                DataColumn col = new DataColumn("col" + i, typeof(string));
                data.Columns.Add(col);
            }

            #region 表头
            DataRow row = data.NewRow();
            row[0] = "区号";
            row[1] = "编号";
            row[2] = "客户姓名";
            row[3] = "服务类别";
            row[4] = "服务项目";
            row[5] = "开始时间";
            row[6] = "结束时间";
            row[7] = "正常天数";
            row[8] = "节假天数";
            row[9] = "服务单号";
            row[10] = "签单人";
            row[11] = "凭证类型";
            row[12] = "凭证号";
            row[13] = "所属公司";
            row[14] = "单价";
            row[15] = "应收";
            row[16] = "折扣";
            row[17] = "调整金额(订单)";
            row[18] = "实收";
            row[19] = "预收";
            row[20] = "结算小计";
            row[21] = "结算调整";
            row[22] = "结算支出";
            row[23] = "管理费";
            row[24] = "备注";
            data.Rows.Add(row);
            #endregion


            double sumWorkDays = 0;
            double sumHolidays = 0;
            double sumReceivable = 0;
            double sumCharge = 0;
            double sumPay = 0;
            double sumPayAdjust = 0;
            double sumManagePay = 0;
            foreach (var item in listData)
            {
                #region 遍历数据
                double workDays = item.Num.HasValue ? item.Num.Value : 0;
                double holidays = item.Holidays.HasValue ? item.Holidays.Value : 0;
                double charge = item.Charge.HasValue ? item.Charge.Value : 0;
                double pay = item.Pay.HasValue ? item.Pay.Value : 0;
                //double receivable = item.ServicePrice * (workDays + 2 * holidays);
                double payAdjust = item.PayAdjust.HasValue ? item.PayAdjust.Value : 0;
                double managePay = charge - pay;

                row = data.NewRow();
                row[0] = item.Area;
                row[1] = item.Bed;
                row[2] = item.CustomerName;
                row[3] = item.Category;
                row[4] = item.ServiceName;
                row[5] = item.Start.HasValue ? item.Start.Value.ToString("yyyy-MM-dd") : string.Empty;
                row[6] = item.End.HasValue ? item.End.Value.ToString("yyyy-MM-dd") : string.Empty;
                row[7] = workDays;
                row[8] = holidays;
                row[9] = item.Invoice;
                row[10] = item.BillSigner;
                row[11] = item.ReceiptType;
                row[12] = item.ReceiptNo;
                row[13] = item.Company;
                row[14] = item.ServicePrice;
                row[15] = charge;// receivable;
                row[16] = item.Discount.HasValue ? item.Discount.Value.ToString() : "-";
                row[17] = item.Adjust.HasValue ? item.Adjust.Value.ToString() : "-";
                row[18] = charge;
                row[19] = item.Prepay.HasValue ? item.Prepay.Value.ToString() : "-";
                row[20] = pay;
                row[21] = payAdjust;
                row[22] = pay + payAdjust;
                row[23] = managePay;
                row[24] = item.Note;
                data.Rows.Add(row);

                sumWorkDays += workDays;
                sumHolidays += holidays;
                sumReceivable += charge;// receivable;
                sumCharge += charge;
                sumPay += pay;
                sumPayAdjust += payAdjust;
                sumManagePay += managePay;
                #endregion
            }

            #region 合计
            row = data.NewRow();
            row[0] = "合计";
            row[1] = string.Empty;
            row[2] = listData.Count + "人";
            row[3] = string.Empty;
            row[4] = string.Empty;
            row[5] = string.Empty;
            row[6] = string.Empty;
            row[7] = sumWorkDays;
            row[8] = sumHolidays;
            row[9] = string.Empty;
            row[10] = string.Empty;
            row[11] = string.Empty;
            row[12] = string.Empty;
            row[13] = string.Empty;
            row[14] = string.Empty;
            row[15] = sumReceivable;
            row[16] = string.Empty;
            row[17] = string.Empty;
            row[18] = sumCharge;
            row[19] = string.Empty;
            row[20] = sumPay;
            row[21] = sumPayAdjust;
            row[22] = sumPay + sumPayAdjust;
            row[23] = sumManagePay;
            row[24] = string.Empty;
            data.Rows.Add(row);
            #endregion

            #region 合并单元格
            //List<string> mergeCells = new List<string>();
            //if (data.Rows.Count > 1)
            //{
            //    //合计
            //    int rowCount = data.Rows.Count;
            //    string item = string.Format("{0}:{1}", "A" + rowCount, "B" + rowCount);
            //    mergeCells.Add(item);
            //}
            #endregion

            string fileName = string.Format("QueryRevenue_{0}_{1}.xlsx", this.GetUserId(), DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss_fff"));
            string configFolder = System.Configuration.ConfigurationManager.AppSettings["AttachmentPath"] + "";
            string filePath = Path.Combine(configFolder, fileName);

            //ExcelExportModel model = new ExcelExportModel
            //{
            //    ExportData = data,
            //    MergeCells = mergeCells
            //};

            //new QueryRevenueExporter().CreatePackage(filePath, model);

            MergeCellParam param = new MergeCellParam();
            param.StartRow = 1;
            param.EndRow = 1;
            param.StartCol = 0;
            param.EndCol = 0;

            ExcelHelper.ExportDataTable(data, filePath, param, 1);

            SubmitResult result = new SubmitResult(true, fileName);
            return base.Json(result, System.Web.Mvc.JsonRequestBehavior.AllowGet);
        }
    }
}
